package deweb.cimaplanning.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

import deweb.cimaplanning.misc;
import deweb.cimaplanning.dto.MonthsPeriodsVersions;

public class MonthsPeriodsVersionsDao extends DBDAO {
		/** Datasource name */
		private static final String dsName = misc.DS_NAME;//$NON-NLS-1$
		
		/**SQL Select all cost elements*/
		protected static final String SQL_SELECT_ALL_ELEMENTS = "SELECT mpv.month_period_version_id, mpv.version_id, mpv.month_period_id, mp.short_name, mp.month, mp.year "
																+ "	FROM months_periods_versions mpv, months_periods mp "
																+ "	WHERE mpv.month_period_id = mp.month_period_id ";
		
		public MonthsPeriodsVersionsDao() {
		}
		
		public ArrayList getArray() throws Exception {
			return getArray(0);
		}
		
		public ArrayList getArray(int versionId) throws Exception {
			StringBuffer sql 			= null;
			MonthsPeriodsVersions dto	= null;
			ArrayList array				= null;
			PreparedStatement pstm		= null;
			ResultSet rs 				= null;
			
			try{
				array = new ArrayList(24);
				
				//la versionId viene integrada en el filters
				
				sql = new StringBuffer(SQL_SELECT_ALL_ELEMENTS);
				
				if (versionId > 0)
					sql.append(" AND version_id " + PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				if (versionId > 0)
					pstm.setInt(1, versionId);
				
				rs = executeQuery(pstm);
				
				while (rs.next()){
					dto = new MonthsPeriodsVersions();
					
					dto.setMonth(rs.getInt("month"));
					dto.setMonthPeriodId(rs.getInt("month_period_id"));
					dto.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
					dto.setShortName(rs.getString("short_name"));
					dto.setVersionId(rs.getInt("version_id"));
					dto.setYear(rs.getInt("year"));

					array.add(dto);
				}
				
				array.trimToSize();
				
			}catch (PoolException poolException){
				Exception e = null;
				poolException.printStackTrace(System.out);
				e = new Exception(poolException);
				throw e;
			} catch(SQLException sqle){
				Exception e = null;
				sqle.printStackTrace(System.out);
				e = new Exception(sqle);
				throw e;
			}finally {
				try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
			}	
			
			return array;		
		}
}
